import pandas as pd
import requests
from bs4 import BeautifulSoup
import json
import time
import csv
import re
from collections import Counter
import pandas as pd
# 清洗函数 删掉一些杂乱的符号
from collections import Counter
def clean_sheet_name(name):
    invalid_chars = ':\\/?*[]'
    for char in invalid_chars:
        name = name.replace(char, '')
    return name
# 读取CSV文件，进行数据清洗和加工
df = pd.read_csv('2.csv')
df = df.dropna(subset=['城市'])
df['最低薪资'] = pd.to_numeric(df['最低薪资'], errors='coerce')
df['最高薪资'] = pd.to_numeric(df['最高薪资'], errors='coerce')
df = df[(df['最低薪资'] != 0) & (df['最低薪资'].notnull())]
selected_columns = ['标准岗位名称', '学历', '最低薪资', '最高薪资', '城市']
df_selected = df[selected_columns]
df_selected = df[selected_columns].copy()
df_selected['平均薪资'] = (df_selected['最低薪资'] + df_selected['最高薪资']) / 2
df_selected['薪资范围'] = df_selected['最高薪资'] - df_selected['最低薪资']
df_selected.to_csv('processed.csv', index=False, encoding='utf-8-sig')

# 统计每个职业出现的次数
df = pd.read_csv('processed.csv')
highest_paid_per_job = df.groupby('标准岗位名称', as_index=False).apply(lambda x: x.loc[x['平均薪资'].idxmax()],
                                                                        include_groups=False).reset_index(drop=True)
highest_paid_per_job.to_csv('highest_paid_per_job.csv', index=False, encoding='utf-8-sig')


# 统计前十名职业出现次数
df = pd.read_csv('processed.csv')
top_jobs = df['标准岗位名称'].value_counts().head(10).index
with pd.ExcelWriter('top_10_jobs.xlsx', engine='openpyxl') as writer:
    for job in top_jobs:
        job_df = df[df['标准岗位名称'] == job]
        clean_name = clean_sheet_name(job[:31])
        job_df.to_excel(writer, sheet_name=clean_name, index=False)

# 统计平均薪资与城市关系
df = pd.read_csv('processed.csv')
grouped = df.groupby('城市')['平均薪资'].transform('max') == df['平均薪资']
highest_paid_per_city = df[grouped]
highest_paid_per_city_sorted = highest_paid_per_city.sort_values(by='平均薪资', ascending=False)
highest_paid_per_city_sorted.to_csv('highest_paid_per_city_sorted.csv', index=False, encoding='utf-8-sig')
